﻿CREATE PROCEDURE s_Borrower_Process_SSN
	@BorrowerID int,
	@SSN_Old sysname
AS

DECLARE @IsEdit bit SET @IsEdit = 0
UPDATE t_Borrower SET SSN = dbo.CleanEditValue(SSN)
WHERE ID = @BorrowerID AND dbo.IsEdit(SSN) = 1

IF @@ROWCOUNT > 0 SET @IsEdit = 1

UPDATE t_Borrower SET SSN = LTRIM(RTRIM(dbo.clr_RegEx_Replace(SSN,'[-\[\]]','',-1,0,1)))
WHERE ID = @BorrowerID AND dbo.clr_RegEx_Match(SSN,'[-\[\]]',1) > ''

DECLARE @SSN sysname,@LoanID int
SELECT @SSN = SSN FROM t_Borrower
WHERE ID = @BorrowerID AND
			isnull([SSN],'') > '' AND
			NOT [SSN] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

IF NOT @SSN IS NULL BEGIN
	ROLLBACK TRAN
	RAISERROR('SSN [%s] must have 9 digits.',16,1,@SSN)
	RETURN
END


DECLARE @Inserted TABLE(ID int, SSN sysname)
INSERT INTO @Inserted SELECT ID,SSN FROM t_Borrower WHERE ID = @BorrowerID AND ISNULL(SSN,'')>''
IF @@ROWCOUNT = 0 RETURN

IF EXISTS(SELECT * FROM @Inserted i
					INNER JOIN t_Borrower B ON i.SSN = B.SSN
					INNER JOIN t_Loan_Borrower L_B ON L_B.BorrowerID = B.ID
					GROUP BY L_B.LoanID,B.SSN HAVING COUNT(*)>1) BEGIN
	SELECT @SSN = B.SSN,@LoanID = L_B.LoanID FROM @Inserted i INNER JOIN t_Borrower B ON i.SSN = B.SSN INNER JOIN t_Loan_Borrower L_B ON L_B.BorrowerID = B.ID GROUP BY L_B.LoanID,B.SSN HAVING COUNT(*)>1	
	ROLLBACK TRAN
	RAISERROR('SSN [%s] belongs to other borrower in the same Loan %d.',1,1,@SSN,@LoanID)
	PRINT 'Dupped SSN'
	RETURN
END	

IF @IsEdit = 1 RETURN

DECLARE @BID int, @Borrower sysname
SELECT @BID = B.ID, @Borrower = dbo.sprint3('Loan:%s - %s[%s]',L_Bs.LoanID,B.SSN,B.FullName)
FROM t_Borrower B
INNER JOIN @Inserted i ON B.SSN = i.SSN AND B.ID <> i.ID
LEFT OUTER JOIN t_Loan_Borrower L_Bs ON L_Bs.BorrowerID = B.ID

IF NOT @Borrower IS NULL BEGIN
	IF (SELECT COUNT(*) FROM t_Loan_Borrower L_B INNER JOIN @Inserted i ON i.ID = L_B.BorrowerID) > 1 BEGIN
		ROLLBACK TRAN
		DECLARE @Text nvarchar(max)
		SELECT @Text = ISNULL(@Text+char(10),'')+dbo.sprint1('LoanID:%s',LoanID)
		FROM t_Loan_Borrower L_B INNER JOIN @Inserted i ON L_B.BorrowerID = i.ID
		RAISERROR('
You have entered SSN that belongs to another borrower,
but current borrower belongs to more then one loan and
system is unable to automaticaly update it.
%s

If you need to correct wrong SSN then use [SSN] notation.
If you need to enter a new borrower - remove this one from the loan
and create a new one from the scratch.
',16,1,@Text)
		RETURN
	END
	UPDATE L_B SET BorrowerID = @BID FROM t_Loan_Borrower L_B INNER JOIN @Inserted i ON i.ID = L_B.BorrowerID
	UPDATE t_Borrower SET SSN = @SSN_Old WHERE ID = @BorrowerID
	INSERT INTO v_Borrower_DeleteIfEmpty SELECT @BorrowerID
--	DECLARE @SSN sysname
--	SELECT @SSN = SSN FROM inserted
--	RAISERROR('Borrower %s already has this SSN:%s.',16,1,@Borrower,@SSN)
--	ROLLBACK TRAN
	RETURN
END

